﻿Imports System.Data.Odbc
Imports System.IO
Imports System.Xml
Imports System.Xml.Schema
Imports System.Net.Mime.MediaTypeNames
Imports System.Reflection
Module CstoneApptCreate

    Sub Main()
        Try
            Dim m_xmld As XmlDocument
            Dim m_nodelist As XmlNodeList
            Dim m_node As XmlNode

            'Create the XML Document
            m_xmld = New XmlDocument()
            'Load the Xml file
            m_xmld.Load("server.xml")
            'Get the list of name nodes 
            m_nodelist = m_xmld.SelectNodes("/server/info")
            'Loop through the nodes
            For Each m_node In m_nodelist
                'Get the firstName Element Value
                Dim dbconn = m_node.Item("dbconn").InnerXml

                Dim days = m_node.Item("days").InnerXml

                Call BuildXMLData(dbconn, days)
            Next
        Catch errorVariable As Exception
            'Error trapping
            Console.Write(errorVariable.ToString())
        End Try

    End Sub
    Sub BuildXMLData(ByVal dbconn, ByVal days)
        Dim conn As OdbcConnection
        Dim comm As OdbcCommand
        Dim dr As OdbcDataReader
        Dim connectionString As String
        Dim sql As String
        Dim xml_doc As New XmlDocument()


        connectionString = dbconn
        sql = "select c.firstname + ' ' + c.lastname as owner, name as Pet, appt_id, a.provider_id as cs_id, " & _
        "convert( char, appt_date , 1) + ' ' + convert(  char, appt_time, 114 ) as appt_date_start, notes, chair_name as location, r.description as reason, num_units " & _
        "from appointment a, patient p, owner o, client c, staff d, chairs ch, reason r " & _
        "where(a.patient_id = p.patientid) " & _
        "and a.client_id = c.clientid " & _
        "and o.patientid = a.patient_id " & _
        "and o.clientid = a.client_id " & _
        "and d.staffid = a.provider_id " & _
        "and a.appt_type_id = r.reasonid " & _
        "and ch.chair_num = a.chair_num " & _
        "and appt_date between dateadd(dd, -1, getdate()) and dateadd(dd, " & days & ", getdate()) " & _
        "order by appt_date, appt_time"

        conn = New OdbcConnection(connectionString)
        conn.Open()
        comm = New OdbcCommand(sql, conn)

        dr = comm.ExecuteReader()

        'sujbect = firstname & " " & lastname & "'s pet, " & pet & " for: " & Reason
        'Dim location = location
        Dim dec As XmlDeclaration = xml_doc.CreateXmlDeclaration("1.0", Nothing, Nothing)
        xml_doc.AppendChild(dec)
        Dim DocRoot As XmlElement = xml_doc.CreateElement("appointments")
        xml_doc.AppendChild(DocRoot)

        While (dr.Read())
            Dim ApptRoot As XmlElement = xml_doc.CreateElement("appt")
            Dim CsidRoot As XmlElement = xml_doc.CreateElement("cs_id")
            Dim ApidRoot As XmlElement = xml_doc.CreateElement("appt_id")
            Dim SubjRoot As XmlElement = xml_doc.CreateElement("subject")
            Dim LocRoot As XmlElement = xml_doc.CreateElement("location")
            Dim BodyRoot As XmlElement = xml_doc.CreateElement("body")
            Dim StartRoot As XmlElement = xml_doc.CreateElement("start")
            Dim EndRoot As XmlElement = xml_doc.CreateElement("end")
            CsidRoot.InnerText = dr.GetValue(3).ToString()
            ApptRoot.AppendChild(CsidRoot)

            ApidRoot.InnerText = dr.GetValue(2).ToString()
            ApptRoot.AppendChild(ApidRoot)

            Dim subject = dr.GetValue(0).ToString() & "'s pet, " & dr.GetValue(1).ToString() & " for: " & dr.GetValue(7).ToString()
            SubjRoot.InnerText = subject
            ApptRoot.AppendChild(SubjRoot)

            LocRoot.InnerText = dr.GetValue(6).ToString()
            ApptRoot.AppendChild(LocRoot)

            BodyRoot.InnerText = dr.GetValue(5).ToString()
            ApptRoot.AppendChild(BodyRoot)

            StartRoot.InnerText = CDate(dr.GetValue(4).ToString())
            ApptRoot.AppendChild(StartRoot)

            Dim minutes = dr.GetValue(8).ToString() * 10 'units * 10 example 3 * 10 = 30
            Dim EndDate = CDate(dr.GetValue(4).ToString())
            EndDate = EndDate.AddMinutes(minutes)
            EndRoot.InnerText = EndDate
            ApptRoot.AppendChild(EndRoot)

            DocRoot.AppendChild(ApptRoot)

            'Console.WriteLine(dr.GetValue(0).ToString())
            'Console.WriteLine(dr.GetValue(1).ToString())
            'Console.WriteLine(dr.GetValue(2).ToString())
        End While
        xml_doc.Save("appointments.xml")
        conn.Close()
        dr.Close()
        comm.Dispose()
        conn.Dispose()

    End Sub

End Module
